/*Test Case Created by Steven Chow
 * This Test Cases tests for all the methods 
 * with in the Customer Database
 * 
 * All Tests are independant from each other.
 */
package com.JUnitTestsDB;
import static org.junit.Assert.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import com.database.AdministratorDB;
import com.database.CustomerDB;

public class CustomerDBTest {
	
	private Connection connection = null;
	private String password;
	private String firstName;
	private String lastName;
	private String email;
	private String username;
	private int phone;
	private int loyaltypoint;
	
	//Setup Connection for Test
	public CustomerDBTest(){
		String connectionString =
			    "jdbc:mysql://localhost/planesimple?"  //CHANGE THIS PART!!!
              + "user=local&password=bluebeariscute89";
		try
        {
            // Ensure the SQL Server driver class is available.
            Class.forName("com.mysql.jdbc.Driver");

            // Establish the connection.
            connection = DriverManager.getConnection(connectionString);
        }
        // Exception handling
        catch (ClassNotFoundException cnfe)  
        {

            System.out.println("ClassNotFoundException " +
                               cnfe.getMessage());
        }
        catch (Exception e)
        {
            System.out.println("Exception " + e.getMessage());
            e.printStackTrace();
        }
	}
	
	//For testing Modify Attributes
	private String GetAttributeFromDB(int columnNum, String tupleName) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + "Customer" + " where username = " +  "'" + tupleName + "'";		
		ResultSet rs = stmt.executeQuery(sqlString);		
		if (rs.next())
		{
			this.username = rs.getString("username");
			switch(columnNum)
			{
			case 1:
				this.password = rs.getString("password");
				return this.password;
			case 2:
				this.firstName = rs.getString("firstname");
				return this.firstName;
			case 3:
				this.lastName = rs.getString("lastname");
				return this.lastName;
			case 4:
				this.email = rs.getString("email");
				return this.email;
			}
		}
		
		//tuple or Column Number Does not exist
		return null;	
	}
	
	//For testing Modify phone and loyalty points
	private int GetPhoneOrPointsFromDB(int flag, String tupleName ) throws SQLException
	{
		Statement stmt = connection.createStatement();
		String sqlString = "select * from " + "Customer" + " where username = " + "'" + tupleName + "'";
		
		ResultSet rs = stmt.executeQuery(sqlString);
		
		if (rs.next())
		{
			this.username = rs.getString("username");
			switch(flag)
			{
				case 0:
					this.phone = Integer.parseInt(rs.getString("phone"));
					return this.phone;
				case 1:
					this.loyaltypoint = Integer.parseInt(rs.getString("loyaltypoint"));
					return this.loyaltypoint;
			}
		}
		return 0;
	}
	
	//Test to make sure the method can delete everything in the database
	//Don't run this unless you want to delete everything in the database
	/*
	@Test
		CustomerDB db = new CustomerDB();
		db.Setup(connection);
		try {
			db.DeleteAll();
			assertEquals(false, db.UserExists("sdf"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
*/
	//Test if we can insert something to the Database and also verify if
	//all the attributes have been added successfully.
	
	//Test 1 if we can delete something in the Database
	@Test
	public void testDelete() {
		CustomerDB db = new CustomerDB("FaggotDeleteme!", "pass", "Blue", "Bear", "BlueBear@hotmail.com", 999, 9999);
		db.Setup(connection);
		try {
			db.Insert();
			db.Delete("FaggotDeleteme!");
			assertEquals(false, db.UserExists("FaggotDeleteme!"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//Test 2 for User does not exist in database
	@Test
	public void testUserDoesNotExists() {
		CustomerDB db = new CustomerDB();
		db.Setup(connection);
		try {
			assertEquals(false, db.UserExists("9999"));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	//Test 3 if we can insert a Blue Bear into the DB
	@Test
	public void testInsert() {
		CustomerDB db = new CustomerDB("BlueBear", "pass", "Blue", "Bear", "BlueBear@hotmail.com", 999, 100);
		db.Setup(connection);
		try {
			db.Insert();
			assertEquals(true, db.UserExists("BlueBear"));
			assertEquals("pass",db.GetPassword());
			assertEquals("Blue", db.GetFirstName());
			assertEquals("Bear", db.GetLastName());
			assertEquals("BlueBear@hotmail.com",db.GetEmail());
			assertEquals(999,db.GetPhone());
			assertEquals(100,db.GetLoyaltyPoint());
			//Always delete all your data after testing it since
			//tests are independant from each other		
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//Test 4 for A Null username
	@Test
	public void testForNullUserName()
	{
		CustomerDB db = new CustomerDB(null, "THIS", "IS", "A", "BUG!!!", 999, 100);
		
		db.Setup(connection);
		try {
			 db.Insert();
			//Always delete all your data after testing it since
			//tests are independant from each other
			assertEquals(false, db.UserExists(null));
		} catch (SQLException e) {
			System.out.println("EXCEPTION BABY!! YOU CAUGHT THE NULL USERNAME!");
			try {
				assertEquals(false, db.UserExists(null));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}  
		}
	}

	//Test 5 for Null password while Inserting into DB
	@Test
	public void testForNullPassword()
	{
		CustomerDB db = new CustomerDB("bugifidontgetdeleted", null, "FIX!", "THIS!", "BUG!", 999, 100);
		
		db.Setup(connection);
		try {
			db.Insert();
			assertEquals(false, db.UserExists("bugifidontgetdeleted"));
			//Always delete all your data after testing it since
			//tests are independant from each other
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				assertEquals(false, db.UserExists("bugifidontgetdeleted"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 6 for Inserting negative phone number
	@Test
	public void testForNegativePhoneNumbers()
	{
		CustomerDB db = new CustomerDB("I", "should", "Not", "be", "HERE!", -999, 100);
		db.Setup(connection);
		try {
			db.Insert();
			assertEquals(false, db.UserExists("I"));
			//Always delete all your data after testing it since
			//tests are independant from each other
		//	db.Delete("Blue");
		} catch (SQLException e) {
			System.out.println("Caught the negative phone number exception!");
			try {
				assertEquals(false, db.UserExists("I"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 7 for Inserting negative Loyalty Points
	@Test
	public void testForNegativeLoyaltyPoints()
	{
		CustomerDB db = new CustomerDB("negativebegone", "I", "AM", "A BUG", "MUHAHAHAHA!!", 999, -100);
		db.Setup(connection);
		try {
			db.Insert();
			assertEquals(false, db.UserExists("negativebegone"));
			//Always delete all your data after testing it since
			//tests are independant from each other
		//	db.Delete("negativebegone");
		} catch (SQLException e) {
			System.out.println("EXCEPTION BABY!! Caught the Negative Points");
			try {
				assertEquals(false, db.UserExists("negativebegone"));
				//db.Delete("negativebegone");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	//Test 8 for Inserting Invalid length of Username
	@Test
	public void testForInvalidLengthOfUsername()
	{
		CustomerDB db = new CustomerDB("Blueddddddddddddddddddddddddddddd", 
				"BUG", "BUG", 
			"BUG", "BUG", 999, 100);
		db.Setup(connection);
		try {
			db.Insert();
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println("Exception here! Caught the Invalid Username Length");
			try {
				assertEquals(false, db.UserExists("Blueddddddddddddddddddddddddddddd"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 9 for Inserting Invalid length of Password
	@Test
	public void testForInvalidLengthOfPassword()
	{
		CustomerDB db = new CustomerDB("FAG", 
				"TOOOOOOOOOOINFINITYANDBEYONDDDDDDDDDDD!!!!!!!!!!!!!!!!!!!!!!!!!!!", "BUG", 
			"BUG", "BUG", 999, 100);
		db.Setup(connection);
		try {
			db.Insert();
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println("Exception here! Caught the Invalid password Length");
			try {
				assertEquals(false, db.UserExists("FAG"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 10 for Inserting Invalid length of 1st Name
	@Test
	public void testForInvalidLengthOfFirstName()
	{
		CustomerDB db = new CustomerDB("1stName", 
				"BUG", "TOOOOOOOOOOINFINITYANDBEYONDDDDDDDDDDD!!!!!!!!!!!!!!!!!!!!!!!!!!!", 
			"BUG", "BUG", 999, 100);
		db.Setup(connection);
		try {
			db.Insert();
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println("Exception here! Caught the Invalid 1st Name Length");
			try {
				assertEquals(false, db.UserExists("1stName"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	//Test 11 for Inserting Invalid length of Last Name
	@Test
	public void testForInvalidLengthOfLastName()
	{
		CustomerDB db = new CustomerDB("LastName", 
				"BUG", "BUG", 
			"TOOOOOOOOOOINFINITYANDBEYONDDDDDDDDDDD!!!!!!!!!!!!!!!!!!!!!!!!!!!", "BUG", 999, 100);
		db.Setup(connection);
		try {
			db.Insert();
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println("Exception here! Caught the Invalid Last Name Length");
			try {
				assertEquals(false, db.UserExists("LastName"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	//Test 12 for Inserting Invalid length of E-mail
	@Test
	public void testForInvalidLengthOfEmail()
	{
		CustomerDB db = new CustomerDB("E-mail", 
				"BUG", "BUG", 
			"BUG", "TOOOOOOOOOOINFINITYANDBEYONDDDDDDDDDDD!!!!!!!!!!!!!!!!!!!!!!!!!!!", 999, 100);
		db.Setup(connection);
		try {
			db.Insert();
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println("Exception here! Caught the Invalid E-mail Length");
			try {
				assertEquals(false, db.UserExists("E-mail"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}

	//Test 13 if you can modify the password in the database
	@Test
	public void testModPassword() {
		CustomerDB db = new CustomerDB("Leina", "pass", "Leina", "Lockster", "GuildWars2Rocks", 999, 800);
		
		db.Setup(connection);
		try {
			db.Insert();
			db.ModPassword("pass1", "Leina");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals(true, db.UserExists("Leina"));
			assertEquals("pass1", GetAttributeFromDB(1, "Leina"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//Test 14 if you can modify the 1st name in the database
	@Test
	public void testModFirstName() {
		CustomerDB db = new CustomerDB("Juvia", "pass", "Leina", "Lockster", "GuildWars2Rocks", 999, 100);
		
		db.Setup(connection);
		try {
			db.Insert();
			db.ModFirstName("Juvia", "Juvia");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals(true, db.UserExists("Juvia"));
			assertEquals("Juvia", GetAttributeFromDB(2, "Juvia"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//Test 15 if you can modify the last name in the database
	@Test
	public void testModLastName() {
		CustomerDB db = new CustomerDB("Lissana", "pass", "Lissana", "Vance", "FairyTail", 999, 100);
		
		db.Setup(connection);
		try {
			db.Insert();
			db.ModLastName("Strauss", "Lissana");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals(true, db.UserExists("Lissana"));
			assertEquals("Strauss", GetAttributeFromDB(3, "Lissana"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//Test 16 if you can modify the e-mail in the database
	@Test
	public void testModEmail() {
		CustomerDB db = new CustomerDB("Kira", "pass", "Kira", "Carsen", "BlueBear@hotmail.com", 999, 100);
		
		db.Setup(connection);
		try {
			db.Insert();
			db.ModEmail("SWTOR", "Kira");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals(true, db.UserExists("Kira"));
			assertEquals("SWTOR", GetAttributeFromDB(4, "Kira"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//Test 17 if you can modify the phone in the database
	@Test
	public void testModPhone() {
		CustomerDB db = new CustomerDB("Leah", "pass", "Leah", "FromDiablo3", "Diablo3", 999, 100);
		
		db.Setup(connection);
		try {
			db.Insert();
			db.ModPhone(666, "Leah");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals(true, db.UserExists("Leah"));
			assertEquals(666, GetPhoneOrPointsFromDB(0,"Leah"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	//Test 18 if you can modify the loyalty points in the database
	@Test
	public void testModPoints() {
		CustomerDB db = new CustomerDB("Kerrigan", "pass", "Kerrigan", "SC2", "HeartOfTheSwarm", 999, 1);
		
		db.Setup(connection);
		try {
			db.Insert();
			db.ModPoints(9999, "Kerrigan");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals(true, db.UserExists("Kerrigan"));
			assertEquals(9999, GetPhoneOrPointsFromDB(1,"Kerrigan"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//Test 19 If you can change the Phone number to negative
	@Test
	public void testModNegativePhone() {
		CustomerDB db = new CustomerDB("NegativePhone", "pass", "Leah", "FromDiablo3", "Diablo3", 999, 100);
		
		db.Setup(connection);
		try {
			db.Insert();
			assertEquals(true, db.UserExists("NegativePhone"));
			db.ModPhone(-999, "NegativePhone");		
			assertEquals(999, GetPhoneOrPointsFromDB(0,"NegativePhone"));
		} catch (SQLException e) {
			System.out.println("Caught the Negative Phone Number Exception");
			try {
				assertEquals(999, GetPhoneOrPointsFromDB(0,"NegativePhone"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 20 If you can change the Loyalty Points to negative
	@Test
	public void testNegativeModPoints() {
		CustomerDB db = new CustomerDB("NegativeMod", "pass", "Kerrigan", "SC2", "HeartOfTheSwarm", 999, 100);
		
		db.Setup(connection);
		try {
			db.Insert();
			db.ModPoints(-100, "NegativeMod");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals(true, db.UserExists("NegativeMod"));
			assertEquals(100, GetPhoneOrPointsFromDB(1,"NegativeMod"));
		} catch (SQLException e) {
			System.out.println("Caught the Negative Points Exception");
			try {
				assertEquals(100, GetPhoneOrPointsFromDB(1,"NegativeMod"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 21 If you can modify the Password to null
	@Test
	public void testModNullPassword() {
		CustomerDB db = new CustomerDB("NullPassword", "pass", "Leina", "Lockster", "GuildWars2Rocks", 999, 800);
		
		db.Setup(connection);
		try {
			db.Insert();
			db.ModPassword(null, "NullPassword");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals(true, db.UserExists("NullPassword"));
			assertEquals("pass", GetAttributeFromDB(1, "NullPassword"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	//Test 22 If you can modify the password to an invalid length
	@Test
	public void testModInvalidLengthPassword() {
		CustomerDB db = new CustomerDB("InvalidModPassword", "pass", "Leina", "Lockster", "GuildWars2Rocks", 999, 800);
		
		db.Setup(connection);
		try {
			//db.Delete("InvalidModLengthPassword");
			db.Insert();
			assertEquals(true, db.UserExists("InvalidModPassword"));
			db.ModPassword("TOOOOOINFINITYANNNNNNDDDDDDDDDDDDBEYOUND!!!!!!!!!!!!!", "InvalidModPassword");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals("pass", GetAttributeFromDB(1, "InvalidModPassword"));
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println("We have caught an Invalid password Length!!");
			try {
				assertEquals(true, db.UserExists("InvalidModPassword"));
				assertEquals("pass", GetAttributeFromDB(1, "InvalidModPassword"));				
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test23 If you can modify the 1st Name to an invalid length
	@Test
	public void testModInvalidLengthFirstName() {
		CustomerDB db = new CustomerDB("InvalidModFirstName", "pass", "Leina", "Lockster", "GuildWars2Rocks", 999, 800);
		
		db.Setup(connection);
		try {
			//db.Delete("InvalidModLengthFirstName");
			db.Insert();
			assertEquals(true, db.UserExists("InvalidModFirstName"));
			db.ModFirstName("TOOOOOINFINITYANNNNNNDDDDDDDDDDDDBEYOUND!!!!!!!!!!!!!", "InvalidModFirstName");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals("Leina", GetAttributeFromDB(2, "InvalidModFirstName"));
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println("We have caught an Invalid First Name Length!!");
			try {
				assertEquals(true, db.UserExists("InvalidModFirstName"));
				assertEquals("Leina", GetAttributeFromDB(2, "InvalidModFirstName"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 24 If you can modify the Last Name to an invalid Length
	@Test
	public void testModInvalidLengthLastName() {
		CustomerDB db = new CustomerDB("InvalidModLastName", "pass", "Leina", "Lockster", "GuildWars2Rocks", 999, 800);
		
		db.Setup(connection);
		try {
			db.Insert();
			assertEquals(true, db.UserExists("InvalidModLastName"));
			db.ModLastName("TOOOOOINFINITYANNNNNNDDDDDDDDDDDDBEYOUND!!!!!!!!!!!!!", "InvalidModLastName");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals("Lockster", GetAttributeFromDB(3, "InvalidModLastName"));
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println("We have caught an Invalid Last Name Length!!");
			try {
				assertEquals(true, db.UserExists("InvalidModLastName"));
				assertEquals("Lockster", GetAttributeFromDB(3, "InvalidModLastName"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
	
	//Test 25 If you can modify the Email to an invalid length
	@Test
	public void testModInvalidLengthEmail() {
		CustomerDB db = new CustomerDB("InvalidModEmail", "pass", "Leina", "Lockster", "GuildWars2Rocks", 999, 800);
		
		db.Setup(connection);
		try {
			db.Insert();
			assertEquals(true, db.UserExists("InvalidModEmail"));
			db.ModEmail("TOOOOOINFINITYANNNNNNDDDDDDDDDDDDghgh56565ghdhfh5656BEYfgfgfgfOUND!!!!!!!!!!!!!jdfakjsdf", "InvalidModEmail");
			//Check to see if Test is passed
			//1st Parameter is the "EXPECTED RESULT" & 2nd Parameter is "ACTUAL RESULTS"
			//If the 2 match, then the test passed.
			assertEquals("GuildWars2Rocks", GetAttributeFromDB(4, "InvalidModEmail"));
		} catch (SQLException e) {
			//e.printStackTrace();
			System.out.println("We have caught an Invalid E-mail Length!!");
			try {
				assertEquals(true, db.UserExists("InvalidModEmail"));
				assertEquals("GuildWars2Rocks", GetAttributeFromDB(4, "InvalidModEmail"));
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
	}
}